const db = require("../db/connMysql");

// 确保连接总是被释放
const releaseConnection = (connection) => {
  try {
    connection.release();
  } catch (e) {
    console.error("释放链接时报错(可忽略，一般是重复释放导致的): ", e);
  }
};

const importDao = {
  /*
   * 导入学生基本信息
   */
  async importStudentsInfo(newStudents, updateStudents) {
    let result = await new Promise((resolve, reject) => {
      db.getConnection((err, connection) => {
        if (err) {
          releaseConnection(connection);
          reject(err);
          return;
        }

        // 开始事务
        connection.beginTransaction(async (err) => {
          if (err) {
            releaseConnection(connection);
            reject(err);
            return;
          }

          // 存储所有操作的结果
          let operationResults = [];

          // 处理新增学生
          newStudents.forEach((student) => {
            operationResults.push(
              new Promise((resolve, reject) => {
                const sql = `INSERT INTO
                    Students(
                        cardId, name, classId, warningLevelId, sex,
                        phone, socialPlatform, address,
                        f_occupation, m_occupation, familyEcon, familyStruct,
                        isOnlyChild, hasFamilyHistoryIll, academic)
                    VALUES (
                        ?, ?, (select classId from Classes where name=? LIMIT 1), 
                        (select warningLevelId from WarningLevels where name=? LIMIT 1), ?,
                        ?, ?, ?,
                        ?, ?, ?, ?,
                        ?, ?, ?
                    )`;
                const i_data = [
                  student["学号"],
                  student["姓名"],
                  student["班级"],
                  "无预警",
                  student["性别"],
                  student["手机号码"],
                  student["社交账号（微信号、QQ号、微博账号等，能掌握的尽量准确提供）"],
                  student["家庭所在地（**省**市）"],
                  student["父亲职业"],
                  student["母亲职业"],
                  student["家庭经济情况"],
                  student["家庭结构"],
                  student["是否独生子女"] == "是" ? "true" : "false",
                  student["家族精神病史"] == "是" ? "true" : "false",
                  student["学业状态"],
                ];
                connection.query(sql, i_data, (queryErr, insertResult) => {
                  if (queryErr) {
                    reject(queryErr);
                    return;
                  }
                  resolve(insertResult);
                });
              })
            );
          });

          // 处理更新学生
          updateStudents.forEach((student) => {
            operationResults.push(
              new Promise((resolve, reject) => {
                const sql = `UPDATE Students SET
                    name=?, 
                    classId=(select classId from Classes where name=? LIMIT 1),
                    sex=?,
                    phone=?,
                    socialPlatform=?,
                    address=?,
                    f_occupation=?,
                    m_occupation=?,
                    familyEcon=?,
                    familyStruct=?,
                    isOnlyChild=?,
                    hasFamilyHistoryIll=?,
                    academic=?
                    WHERE cardId=?`;
                const u_data = [
                  student["姓名"],
                  student["班级"],
                  student["性别"],
                  student["手机号码"],
                  student["社交账号（微信号、QQ号、微博账号等，能掌握的尽量准确提供）"],
                  student["家庭所在地（**省**市）"],
                  student["父亲职业"],
                  student["母亲职业"],
                  student["家庭经济情况"],
                  student["家庭结构"],
                  student["是否独生子女"] == "是" ? "true" : "false",
                  student["家族精神病史"] == "是" ? "true" : "false",
                  student["学业状态"],
                  student["学号"]
                ];
                connection.query(sql, u_data, (queryErr, updateResult) => {
                  if (queryErr) {
                    reject(queryErr);
                    return;
                  }
                  resolve(updateResult);
                });
              })
            );
          });

          try {
            const results = await Promise.all(operationResults);
            connection.commit((err) => {
              if (err) {
                connection.rollback(() => {
                  releaseConnection(connection);
                  reject(err);
                });
                return;
              }
              releaseConnection(connection);
              resolve(results);
            });
          } catch (e) {
            connection.rollback(() => {
              releaseConnection(connection);
              reject(e);
            });
          }
        });
      });
    });
    return result;
  },
  /*
   * 导入普查访谈记录
   */
  async importCensusInterviewRecord(data) {
    let result = await new Promise((resolve, reject) => {
      db.getConnection((err, connection) => {
        if (err) {
          // 连不上数据库 则 释放连接
          releaseConnection(connection);
          reject(err);
          return;
        }

        // 开始事务
        connection.beginTransaction(async (err) => {
          if (err) {
            releaseConnection(connection);
            reject(err);
            return;
          }

          // 存储所有插入操作的结果
          let insertResults = [];
          let errorOccurred = false;

          data.forEach((item) => {
            // 遍历普查访谈记录的数据并逐个插入
            insertResults.push(
              new Promise((resolve, reject) => {
                const sql = `INSERT INTO
                                           CensusVisitInterviewRecords(
                                               studentId, startTime, endTime, counselorId, question1,
                                               question2, question3, question4, question5, question6,
                                               question7, note, centerOpinionId, warningLevelId)
                                       VALUES (
                                           (select studentId from Students where cardId = ? and name = ? LIMIT 1),
                                           ?, ?, (select uid as counselorId from Users where username = ?), ?, ?, ?, ?, ?, ?, ?, ?,
                                           (select centerOpinionId from CenterOpinion where centerOpinion = ?),
                                           (select warningLevelId from WarningLevels where name = ?)
                                       )`;
                const i_data = [
                  item["学号"],
                  item["姓名"],
                  item["访谈日期"],
                  item["访谈日期"],
                  item["访谈人"],
                  item["1. 最近的情绪怎么样？"],
                  item["2. 最近的学习、生活怎样？有什么困难吗？"],
                  item[
                    "3. 人际关系  有好朋友吗？都是谁？舍友相处的怎样？和谁关系近？"
                  ],
                  item["4. 对学校的喜好及适应"],
                  item["5. 家庭情况及成长史（选填）"],
                  item["6. 睡眠、身体、精神状况等。是否做过心理咨询？"],
                  item["7. 是否有自杀的想法？频次、时间等（选填）"],
                  item["备注（选填）"],
                  item["中心意见"],
                  item["预警级别"],
                ];

                try {
                  connection.query(sql, i_data, (queryErr, insertResult) => {
                    if (queryErr) {
                      errorOccurred = true;
                      console.error("Error inserting item:", queryErr);
                      // 如果有错误发生，则立即回滚事务
                      connection.rollback(() => {
                        releaseConnection(connection);
                        reject(queryErr);
                      });
                      return;
                    }
                    resolve(insertResult);
                  });
                } catch (e) {
                  console.error("Error occurred during item insertion:", e);
                  connection.rollback(() => {
                    releaseConnection(connection);
                    reject(e);
                  });
                }
              })
            );
          });

          // 尝试等待所有导入的信息
          try {
            await Promise.all(insertResults);
          } catch (e) {
            // 有一个插入操作失败，则回滚事务
            connection.rollback(() => {
              releaseConnection(connection);
              reject(e);
            });
            return;
          }

          // 如果所有插入操作都成功，则提交事务
          connection.commit((err) => {
            if (err) {
              // 如果提交出错，则回滚事务
              connection.rollback(() => {
                releaseConnection(connection);
                reject(err);
              });
              return;
            }

            // 释放连接
            releaseConnection(connection);
            resolve(insertResults);
          });
        });
      });
    });
    return result;
  },
  /*
   * 导入测评记录
   */
  async importAssessRecords(data) {
    let result = await new Promise((resolve, reject) => {
      db.getConnection((err, connection) => {
        if (err) {
          // 连不上数据库
          releaseConnection(connection);
          reject(err);
          return;
        }
        // 开始事务
        connection.beginTransaction(async (err) => {
          if (err) {
            releaseConnection(connection);
            reject(err);
            return;
          }

          // 存储所有插入操作的结果
          let insertResults = [];
          let errorOccurred = false;

          data.forEach((item) => {
            // 遍历普查访谈记录的数据并逐个插入
            insertResults.push(
              new Promise((resolve, reject) => {
                const sql = `INSERT INTO
                                           AssessRecords(studentId, assessTime, testName, score, level)
                                         VALUES (
                                           (select studentId from Students where cardId = ? and name = ? LIMIT 1),
                                           ?, ?, ?, ?
                                         )`;
                const i_data = [
                  item["学号"],
                  item["姓名"],
                  item["评测时间"],
                  item["问卷名称"] || '暂无名称',
                  item["问卷分数"] || '0',
                  item["分类级别"] || '无',
                ];
                try {
                  connection.query(sql, i_data, (queryErr, insertResult) => {
                    if (queryErr) {
                      errorOccurred = true;
                      console.error("Error inserting item:", queryErr);
                      // 如果有错误发生，则立即回滚事务
                      connection.rollback(() => {
                        releaseConnection(connection);
                        reject(queryErr);
                      });
                      return;
                    }
                    resolve(insertResult);
                  });
                } catch (e) {
                  console.error("Error occurred during item insertion:", e);
                  connection.rollback(() => {
                    releaseConnection(connection);
                    reject(e);
                  });
                }
              })
            );
          });

          // 尝试等待所有导入的信息
          try {
            await Promise.all(insertResults);
          } catch (e) {
            // 有一个插入操作失败，则回滚事务
            connection.rollback(function () {
              releaseConnection(connection);
              reject(e);
            });
            return;
          }

          // 如果所有插入操作都成功，则提交事务
          connection.commit((err) => {
            if (err) {
              // 如果提交出错，则回滚事务
              connection.rollback(() => {
                releaseConnection(connection);
                reject(err);
              });
              return;
            }

            // 释放连接
            releaseConnection(connection);
            resolve(insertResults);
          });
        });
      });
    });
    return result;
  },
};

module.exports = importDao;
